<?php

namespace AdminBundle\Repository;


class HuobiCalcKline1minRepository extends BaseRepository
{
    /**
     *
     * @param $alias_key
     * @param int $status ; 0=正常,1=暂停
     * @return mixed
     * @throws \Exception
     */
    public function isPauseForMode($alias_key, $status = 1)
    {
        $sql = 'select * from huobi_switch_model where alias_key = :alias_key and status = :status';
        $params = ['alias_key' => $alias_key, 'status' => $status];

        $result = $this->rawQuery($sql, $params);

        return $result ? true : false;
    }

    /**
     * @param int $length 获取数据的长度
     * @param int $duration 时间跨度
     * @param int $coin 币
     * @param int $timestamp 时间戳
     * @return bool
     *
     * @throws \Exception
     */
    public function getAmazing1_0($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_1_0(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);

    }

    public function getAmazing1_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_1_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);

    }

    public function getAmazing2_0($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_2_0(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing2_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_2_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing2_2($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_2_2(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing2_3($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_2_3(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing2_7($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_2_7(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing2_8($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_2_8(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing3_0($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_3_0(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing3_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_3_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing3_3($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_3_3(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing3_4($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_3_4(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing3_5($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_3_5(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    public function getAmazing6_0($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_amazing_6_0(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }


    public function getTrack1_0($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_track_1_0(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);

    }

    public function getTrack1_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_track_1_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);

    }

    public function getTrack1_2($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_track_1_2(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);

    }

    public function getTrack1_3($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_track_1_3(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures6_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_6_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures6_2($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_6_2(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures6_3($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_6_3(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures6_3_5($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_6_3_5(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures6_4($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_6_4(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures6_4_5($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_6_4_5(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_2($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_2(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_3($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_3(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_4($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_4(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_5($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_5(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_6($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_6(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_7($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_7(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures7_8($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_7_8(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }


    /**
     * 开多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures8_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_futures_open_long_on_8_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures8_2($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_futures_open_short_on_8_2(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平多
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures8_3($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_futures_open_long_off_8_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 平空
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures8_4($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_futures_open_short_off_8_2(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开多对冲
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures_9_1($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_9_1(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }

    /**
     * 开多对冲
     * @param int $length
     * @param int $duration
     * @param string $coin
     * @param int $timestamp
     * @return bool
     * @throws \Exception
     */
    public function getFutures_9_3($length = 20, $duration = 1440, $coin = '', $timestamp = 0)
    {
        $sql = "call proc_get_futures_9_3(:length,:time_stamp,:duration, :coin)";
        $timestamp = $timestamp ?: time();
        $params = ['length' => $length, 'time_stamp' => $timestamp, 'duration' => $duration, 'coin' => $coin];

        return $this->rawQuery($sql, $params);
    }


    public function getAmazing_delete($length = 20, $duration = 1440, $timestamp = 0)
    {
        $sql = <<<EOF
SELECT 
    id,
    coin,
    coin_time,
    ctime,
    open_1min as `open`,
    close_1min as `close`,
    amount_1min,
    ((close_1min - open_10min) / 10) / ABS((close_10min - open_30min + 0.000000001) / 20) AS h10b1030,
    ((close_1min - open_10min) / 10) / ABS((close_10min - open_60min + 0.000000001) / 50) AS h10b1060,
    ((close_1min - open_5min) / 5) / ABS((close_10min - open_15min + 0.000000001) / 5) AS h5b1015
FROM
    huobi_calc_kline_1min
WHERE
    time_stamp > :time_stamp
--    AND up_count_5mins > 3
        AND NOT (amount_1min = 0 OR amount_sum_3mins = 0
        OR (close_1min - open_3min) = 0)
        AND (close_1min - open_3min) / (close_1min) > 0.012
        AND (close_1min - open_3min) - ABS(close_10min - open_30min) > 0
        AND (close_1min - open_5min) - ABS(close_1min - open_3min) > 0
        AND (close_1min - open_10min) - ABS(close_1min - open_5min) > 0
        AND (close_1min - open_3min) - ABS(close_10min - open_15min) > 0
        AND (close_1min - open_3min) - ABS(close_15min - open_30min) > 0
        AND (close_1min - open_5min) - ABS(close_30min - open_60min) > 0
        AND ((amount_sum_3mins / 3) / ((amount_sum_60mins + 0.001) / 60)) > 1
        AND ((amount_1min) / ((amount_sum_60mins + 0.001) / 60)) > 1    
ORDER BY id DESC        
LIMIT {$length} 
EOF;
        $timestamp = $timestamp ?: time();
        $params = ['time_stamp' => $timestamp - 60 * $duration];
        return $this->rawQuery($sql, $params);
    }

}
